﻿-- =============================================
-- Author:		Dmitry Nazarov
-- Create date: 08.03.2011
-- Description:	
-- =============================================
CREATE PROCEDURE [dbo].[report_payments_in]
	(
		@p_date_from	date,
		@p_date_to		date,
		@p_nsi_organization_id	int,
		@p_contract_id	int,
		@p_nsi_type_of_activity_id	int,
		@p_sum_from money,
		@p_sum_to money
	)
AS
BEGIN
	SET NOCOUNT ON;

    SELECT     o.SHORT_NAME AS ORG_NAME, c.NUMBER AS CONTRACT_NUMBER, pi.NUMBER, pi.DATE, pi.SUM_RUB, c.NSI_TYPE_OF_ACTIVITY_ID, 
                      io.NUMBER AS INVOICE_NUMBER, pio.SUM_RUB AS PAY_ON_INV_SUM, pi.SUM_RUB/dbo.CountPays(pi.ID) as PROPORTION_SUM
FROM         CONTRACT AS c INNER JOIN
                      PID ON c.PID = PID.ID RIGHT OUTER JOIN
                      PAYMENT_IN AS pi ON PID.ID = pi.PID LEFT OUTER JOIN
                      NSI_ORGANIZATION AS o ON pi.NSI_ORGANIZATION_ID = o.ID LEFT OUTER JOIN
                      PAY_IN_ON_INV_OUT AS pio ON pio.PAYMENT_IN_ID = pi.ID LEFT OUTER JOIN
                      INVOICE_OUT AS io ON io.ID = pio.INVOICE_OUT_ID
WHERE     ((pi.DATE BETWEEN @p_date_from AND @p_date_to) AND (@p_nsi_type_of_activity_id IS NULL) AND (@p_contract_id IS NULL) AND 
                      (@p_nsi_organization_id IS NULL) OR
                      (pi.DATE BETWEEN @p_date_from AND @p_date_to) AND (@p_contract_id IS NULL) AND (@p_nsi_organization_id IS NULL) AND 
                      (c.NSI_TYPE_OF_ACTIVITY_ID = @p_nsi_type_of_activity_id) OR
                      (pi.DATE BETWEEN @p_date_from AND @p_date_to) AND (@p_nsi_type_of_activity_id IS NULL) AND (@p_nsi_organization_id IS NULL) AND 
                      (c.ID = @p_contract_id) OR
                      (pi.DATE BETWEEN @p_date_from AND @p_date_to) AND (@p_nsi_organization_id IS NULL) AND 
                      (c.NSI_TYPE_OF_ACTIVITY_ID = @p_nsi_type_of_activity_id) AND (c.ID = @p_contract_id) OR
                      (pi.DATE BETWEEN @p_date_from AND @p_date_to) AND (@p_nsi_type_of_activity_id IS NULL) AND (@p_contract_id IS NULL) AND 
                      (o.ID = @p_nsi_organization_id) OR
                      (pi.DATE BETWEEN @p_date_from AND @p_date_to) AND (@p_contract_id IS NULL) AND (c.NSI_TYPE_OF_ACTIVITY_ID = @p_nsi_type_of_activity_id)
                       AND (o.ID = @p_nsi_organization_id) OR
                      (pi.DATE BETWEEN @p_date_from AND @p_date_to) AND (@p_nsi_type_of_activity_id IS NULL) AND (c.ID = @p_contract_id) AND 
                      (o.ID = @p_nsi_organization_id) OR
                      (pi.DATE BETWEEN @p_date_from AND @p_date_to) AND (c.NSI_TYPE_OF_ACTIVITY_ID = @p_nsi_type_of_activity_id) AND (c.ID = @p_contract_id) 
                      AND (o.ID = @p_nsi_organization_id))
                      AND ((@p_sum_from is NULL) OR (pi.SUM_RUB BETWEEN @p_sum_from AND @p_sum_to))
END